UPDATE Statement

Course- Oracle/PLSQL >

This Oracle tutorial explains how to use the Oracle UPDATE statement with syntax, examples, and practice exercises.

Description

The Oracle UPDATE statement is used to update existing records in a table in an Oracle database. There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.

Syntax

The syntax for the UPDATE statement when updating one table in Oracle/PLSQL is:

UPDATE table

SET column1 = expression1,

    column2 = expression2,

    ...

    column_n = expression_n

[WHERE conditions];

OR

The syntax for the Oracle UPDATE statement when updating one table with data from another table is:

UPDATE table1

SET column1 = (SELECT expression1

               FROM table2

               WHERE conditions)

[WHERE conditions];

Parameters or Arguments

column1, column2, ... column_n

The columns that you wish to update.

expression1, expression2, ... expression_n

The new values to assign to the column1, column2, ... column_n. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

WHERE conditions

Optional. The conditions that must be met for the update to execute. If no conditions are provided, then all records in the table will be updated.

Example - Update single column

Let's look at a very simple Oracle UPDATE query example.

UPDATE customers

SET last_name = 'Anderson'

WHERE customer_id = 5000;

This Oracle UPDATE example would update the last_name to 'Anderson' in the customers table where the customer_id is 5000.

Example - Update multiple columns

Let's look at an Oracle UPDATE example where you might want to update more than one column with a single UPDATE statement.

UPDATE customers

SET state = 'California',

    customer_rep = 32

WHERE customer_id > 100;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This Oracle UPDATE statement example would update the state to 'California' and the customer_rep to 32 where the customer_id is greater than 100.

Example - Update table with data from another table

Let's look at an Oracle UPDATE example that shows how to update a table with data from another table.

UPDATE customers

SET c_details = (SELECT contract_date

                 FROM suppliers

                 WHERE suppliers.supplier_name = customers.customer_name)

WHERE customer_id < 1000;

This UPDATE example would update only the customers table for all records where the customer_id is less than 1000. When the supplier_name from the suppliers table matches the customer_name from the customers table, the contract_date from the suppliers table would be copied to the c_details field in the customers table.

Example - Using EXISTS Clause

You can also perform more complicated updates in Oracle.

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the Oracle UPDATE statement, you can use the Oracle EXISTS clause.

For example:

UPDATE suppliers

SET supplier_name = (SELECT customers.customer_name

                     FROM customers

                     WHERE customers.customer_id = suppliers.supplier_id)

WHERE EXISTS (SELECT customers.customer_name

              FROM customers

              WHERE customers.customer_id = suppliers.supplier_id);

In this Oracle UPDATE example, whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer_name from the customers table.

Practice Exercise #1:

Based on the suppliers table populated with the following data, update the city to "San Francisco" for all records whose supplier_name is "IBM".

CREATE TABLE suppliers

( supplier_id number(10) not null,

  supplier_name varchar2(50) not null,

  city varchar2(50),

  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)

);

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5001, 'Microsoft', 'Chicago');

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5002, 'IBM', 'Chicago');

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5003, 'Red Hat', 'Detroit');

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5004, 'NVIDIA', 'New York');

Solution for Practice Exercise #1:

The following UPDATE statement would perform this update in Oracle.

UPDATE suppliers

SET city = 'San Francisco'

WHERE supplier_name = 'IBM';

The suppliers table would now look like this:

SUPPLIER_ID

SUPPLIER_NAME

CITY

5001

Microsoft

Chicago

5002

IBM

San Francisco

5003

Red Hat

Detroit

5004

NVIDIA

New York

Practice Exercise #2:

Based on the suppliers and customers table populated with the following data, update the city in the suppliers table with the city in the customers table when the supplier_name in the suppliers table matches the customer_name in the customers table.

CREATE TABLE suppliers

( supplier_id number(10) not null,

  supplier_name varchar2(50) not null,

  city varchar2(50),

  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)

);

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5001, 'Microsoft', 'New York');

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5002, 'IBM', 'Chicago');

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5003, 'Red Hat', 'Detroit');

 

INSERT INTO suppliers (supplier_id, supplier_name, city)

VALUES (5005, 'NVIDIA', 'LA');

 

CREATE TABLE customers

( customer_id number(10) not null,

  customer_name varchar2(50) not null,

  city varchar2(50),

  CONSTRAINT customers_pk PRIMARY KEY (customer_id)

);

 

INSERT INTO customers (customer_id, customer_name, city)

VALUES (7001, 'Microsoft', 'San Francisco');

 

INSERT INTO customers (customer_id, customer_name, city)

VALUES (7002, 'IBM', 'Toronto');

 

INSERT INTO customers (customer_id, customer_name, city)

VALUES (7003, 'Red Hat', 'Newark');

Solution for Practice Exercise #2:

The following UPDATE statement would perform this update in Oracle.

UPDATE suppliers

SET city = (SELECT customers.city

            FROM customers

            WHERE customers.customer_name = suppliers.supplier_name)

WHERE EXISTS (SELECT customers.city

              FROM customers

              WHERE customers.customer_name = suppliers.supplier_name);

The suppliers table would now look like this:

SUPPLIER_ID

SUPPLIER_NAME

CITY

5001

Microsoft

San Francisco

5002

IBM

Toronto

5003

Red Hat

Newark

5004

NVIDIA

LA